package god.dream.comment.dao;

import god.dream.comment.model.Comment;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import org.springframework.dao.DataAccessException;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.simple.ParameterizedRowMapper;
import org.springframework.jdbc.core.simple.SimpleJdbcDaoSupport;

public class SpringJdbcCommentDao extends SimpleJdbcDaoSupport implements
		CommentDAO {
	public int insert(Comment comment) throws DataAccessException {
		String query = "INSERT INTO "
				+ "COMMENT ( P_ID, P_UID, P_DATE, P_VALUE, P_COMMENT ) "
				+ "VALUES ( ?, ?, ?, ?, ?)";
		return getSimpleJdbcTemplate().update(query, comment.insert());
	}

	public int delete(int id) throws DataAccessException {
		String query = "DELETE FROM COMMENT WHERE P_ID=?";
		return getSimpleJdbcTemplate().update(query, new Object[] { id });
	}

	public Comment findComment(int id) throws DataAccessException {
		String query = "SELECT * " + "FROM COMMENT WHERE P_ID=?";
		ParameterizedRowMapper<Comment> rowMapper = getRowMapper();

		try {
			return getSimpleJdbcTemplate().queryForObject(query, rowMapper,
					new Object[] { id });
		} catch (EmptyResultDataAccessException e) {
			return null;
		}
	}

	public List<Comment> findCommentList() throws DataAccessException {
		String sql = "SELECT * FROM COMMENT";
		ParameterizedRowMapper<Comment> rowMapper = getRowMapper();

		return getSimpleJdbcTemplate().query(sql, rowMapper);
	}

	public List<Comment> findCommentListWithUser(int uid) throws DataAccessException {
		String sql = "SELECT * FROM COMMENT " +
					"WHERE P_UID=? ORDER BY P_DATE";
		ParameterizedRowMapper<Comment> rowMapper = getRowMapper();

		return getSimpleJdbcTemplate().query(sql, rowMapper, new Object[] { uid});
	}

	public int update(Comment comment) throws DataAccessException {
		String query = "UPDATE COMMENT "
				+ "SET P_UID=?, P_DATE=?, P_VALUE=?, P_COMMENT=? "
				+ "WHERE P_ID=?";
		return getSimpleJdbcTemplate().update(query, comment.update());
	}

	private ParameterizedRowMapper<Comment> getRowMapper() {
		ParameterizedRowMapper<Comment> rowMapper = new ParameterizedRowMapper<Comment>() {
			public Comment mapRow(ResultSet rs, int rownum) throws SQLException {
				Comment comment = new Comment();
				comment.setId(rs.getInt("P_ID"));
				comment.setUid(rs.getInt("P_UID"));
				comment.setValue(rs.getInt("P_VALUE"));
				comment.setDate(rs.getDate("P_DATE").toString());
				comment.setCommValue(rs.getString("P_COMMENT"));
				return comment;
			}
		};
		return rowMapper;
	}
}
